GROUP BY에서 집계 기준을 잘못 잡는 흔한 실수

목차

• #문제가 되는 상황
• #먼저 결과의 grain을 한 문장으로 쓴다
• #GROUP BY 컬럼이 결과의 행을 결정한다
• #비집계 컬럼을 임의로 선택하지 않는다
• #1대N JOIN 뒤 합계가 부풀어 오르는 이유
• #여러 1대N 관계는 먼저 각각 집계한다
• #WHERE와 HAVING의 처리 대상
• #조건부 집계에서 NULL을 주의한다
• #COUNT DISTINCT로 증상을 숨기지 않는다
• #날짜 집계의 timezone과 경계
• #실전 점검 목록
• #결론
• #관련 노트

문제가 되는 상황

월별 매출을 구했는데 실제 결제 금액의 두 배가 나오거나, 고객별 마지막 주문 상태를 조회했는데 어떤 상태가 선택될지 실행할 때마다 달라지는 경우가 있다. 집계 함수 자체보다 GROUP BY 전에 만들어진 행의 단위가 잘못된 경우가 많다.

집계 query를 작성하기 전에는 “결과 한 행이 무엇을 의미하는가?”를 한 문장으로 정의해야 한다. 고객 한 명인지, 고객과 월의 조합인지, 주문 한 건인지가 정해져야 필요한 GROUP BY와 JOIN 순서를 결정할 수 있다.

먼저 결과의 grain을 한 문장으로 쓴다

grain은 결과 행 하나가 나타내는 최소 단위다.

잘못된 요구: 고객 매출을 보여 준다.
명확한 grain: 결과 한 행은 한 고객의 한 달 결제 합계다.

필요한 key는 (customer_id, sales_month)가 된다.

SELECT
  o.customer_id,
  DATE_FORMAT(o.paid_at, '%Y-%m-01') AS sales_month,
  SUM(o.total_minor) AS sales_total_minor
FROM orders o
WHERE o.status = 'paid'
GROUP BY
  o.customer_id,
  DATE_FORMAT(o.paid_at, '%Y-%m-01');

DB별 날짜 함수가 다르므로 예시는 MySQL 형태다. 운영에서는 timezone과 index 사용까지 별도로 고려한다.

집계 중간 단계마다 grain을 메모하면 복잡한 query가 읽기 쉬워진다.

orders: 주문 1건당 1행
item_totals: 주문 1건당 1행
monthly_sales: 고객+월당 1행

GROUP BY 컬럼이 결과의 행을 결정한다

고객별 합계와 고객·상태별 합계는 다른 결과다.

-- 고객당 1행
SELECT customer_id, SUM(total_minor)
FROM orders
GROUP BY customer_id;

-- 고객과 상태 조합당 1행
SELECT customer_id, status, SUM(total_minor)
FROM orders
GROUP BY customer_id, status;

두 번째 query에 status를 추가하면 같은 고객이 여러 행으로 나뉜다. 화면이 고객당 한 행을 기대하는데 “에러를 없애기 위해” SELECT의 모든 컬럼을 GROUP BY에 추가하면 grain 자체가 바뀐다.

flowchart LR
    O[주문 행] --> G1[GROUP BY customer_id]
    G1 --> R1[고객당 1행]
    O --> G2[GROUP BY customer_id, status]
    G2 --> R2[고객·상태당 1행]

비집계 컬럼을 임의로 선택하지 않는다

일부 DB 설정은 GROUP BY에 없는 비집계 컬럼을 SELECT하도록 허용할 수 있다.

SELECT
  customer_id,
  status,
  MAX(created_at) AS latest_created_at
FROM orders
GROUP BY customer_id;

MAX(created_at)와 같은 행의 status가 선택된다는 보장이 없다. customer group 안의 임의 status가 나올 수 있다.

최신 주문 행 전체가 필요하면 window function으로 행을 먼저 고른다.

WITH ranked_orders AS (
  SELECT
    o.*,
    ROW_NUMBER() OVER (
      PARTITION BY o.customer_id
      ORDER BY o.created_at DESC, o.id DESC
    ) AS row_num
  FROM orders o
)
SELECT customer_id, id, status, created_at
FROM ranked_orders
WHERE row_num = 1;

id DESC는 created_at 동률의 순서를 고정한다. MySQL의 ONLY_FULL_GROUP_BY처럼 잘못된 비집계 컬럼을 거부하는 설정을 유지하는 편이 오류를 빨리 발견하게 돕는다.

1대N JOIN 뒤 합계가 부풀어 오르는 이유

orders 한 건에 item 두 개가 있다고 하자. orders의 total_minor는 주문당 한 번 저장되어 있지만 item과 JOIN하면 두 행에 반복된다.

SELECT o.id, o.total_minor, oi.line_no
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.id = 1001;

여기서 SUM(o.total_minor)은 160000이 된다. 주문 금액을 합산하려면 item JOIN이 정말 필요한지 확인하거나 주문 grain으로 다시 줄여야 한다.

SELECT SUM(o.total_minor)
FROM orders o
WHERE o.status = 'paid';

item 조건이 있는 주문만 선택하려는 목적이라면 EXISTS가 행을 늘리지 않는다.

SELECT SUM(o.total_minor)
FROM orders o
WHERE o.status = 'paid'
  AND EXISTS (
    SELECT 1
    FROM order_items oi
    WHERE oi.order_id = o.id
      AND oi.product_id = :product_id
  );

여러 1대N 관계는 먼저 각각 집계한다

주문에 item 두 개와 coupon 세 개가 있으면 모두 JOIN한 중간 결과는 최대 6행이다.

orders 1
× order_items 2
× order_coupons 3
= 6 rows

각 관계를 주문 grain으로 먼저 집계한다.

WITH item_totals AS (
  SELECT
    order_id,
    COUNT(*) AS item_count,
    SUM(quantity * unit_price_minor) AS item_total
  FROM order_items
  GROUP BY order_id
),
coupon_totals AS (
  SELECT
    order_id,
    COUNT(*) AS coupon_count,
    SUM(discount_minor) AS discount_total
  FROM order_coupons
  GROUP BY order_id
)
SELECT
  o.id,
  COALESCE(i.item_count, 0) AS item_count,
  COALESCE(i.item_total, 0) AS item_total,
  COALESCE(c.coupon_count, 0) AS coupon_count,
  COALESCE(c.discount_total, 0) AS discount_total
FROM orders o
LEFT JOIN item_totals i ON i.order_id = o.id
LEFT JOIN coupon_totals c ON c.order_id = o.id;

각 CTE가 주문당 최대 한 행이므로 마지막 JOIN에서 곱셈이 생기지 않는다.

WHERE와 HAVING의 처리 대상

WHERE는 집계 전에 원본 행을 걸러내고 HAVING은 GROUP BY 뒤 집계 결과를 걸러낸다.

SELECT
  customer_id,
  SUM(total_minor) AS paid_total
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING SUM(total_minor) >= 100000;

이 query는 paid 주문만 입력으로 사용한 뒤 합계가 100000 이상인 고객 group을 남긴다.

일반 행 조건을 모두 HAVING에 두면 optimizer가 pushdown할 수도 있지만 의도가 흐려지고 집계 입력이 커질 수 있다. 행 조건은 WHERE, 집계 조건은 HAVING으로 구분한다.

LEFT JOIN 오른쪽 행을 WHERE에서 필터링하면 왼쪽 보존 의미가 사라질 수 있다는 점도 JOIN 조건 위치와 연결된다.

조건부 집계에서 NULL을 주의한다

상태별 건수를 한 행에 표현할 때 CASE를 사용할 수 있다.

SELECT
  customer_id,
  SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_count,
  SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_count
FROM orders
GROUP BY customer_id;

ELSE 0을 빼면 조건에 맞지 않는 행에서 NULL이 되고, group 전체가 조건을 만족하지 않으면 SUM 결과도 NULL일 수 있다.

SUM(CASE WHEN status = 'paid' THEN total_minor ELSE 0 END)

COUNT(CASE WHEN condition THEN 1 END)은 NULL이 아닌 값만 세는 특성을 이용한다.

COUNT(CASE WHEN status = 'paid' THEN 1 END)

팀에서 한 패턴을 정하고 NULL 동작을 테스트한다. DB가 FILTER 절을 지원하면 더 직접적인 표현을 쓸 수 있다.

COUNT DISTINCT로 증상을 숨기지 않는다

JOIN 중복 때문에 count가 크다고 무조건 COUNT(DISTINCT id)를 붙이면 결과 숫자는 맞아 보여도 중간 결과는 여전히 폭증한다.

SELECT COUNT(DISTINCT o.id)
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN order_coupons oc ON oc.order_id = o.id;

정말 “item과 coupon이 모두 있는 주문 수”가 필요하다면 EXISTS 두 개가 grain을 유지한다.

SELECT COUNT(*)
FROM orders o
WHERE EXISTS (
  SELECT 1 FROM order_items oi WHERE oi.order_id = o.id
)
AND EXISTS (
  SELECT 1 FROM order_coupons oc WHERE oc.order_id = o.id
);

DISTINCT가 의미상 필요한 경우도 있다. 하지만 왜 중복이 생기는지 cardinality를 확인한 뒤 사용한다.

날짜 집계의 timezone과 경계

UTC timestamp를 바로 DATE로 잘라 한국 일별 매출을 구하면 현지 자정 경계가 어긋난다. 먼저 보고서 timezone으로 변환하거나 UTC 범위를 계산한다.

KST 2025-04-15 00:00:00
→ UTC 2025-04-14 15:00:00

index를 활용하려면 timestamp 컬럼에 함수를 적용하기보다 반열린 UTC 범위로 필터링하는 방법을 검토한다.

WHERE paid_at >= :from_utc
  AND paid_at < :to_utc

그 후 결과 표시나 group key를 업무 timezone 기준으로 만든다. DST가 있는 timezone은 하루가 항상 24시간이 아니므로 단순히 86400초를 더하지 않고 timezone-aware date library로 경계를 계산한다.

실전 점검 목록

집계 전에 결과의 grain을 한 문장으로 정의한다. 각 일대다 관계를 먼저 원하는 단위로 집계한 뒤 결합하면 중복 합계를 줄일 수 있다.

결론

GROUP BY 오류를 줄이는 가장 좋은 방법은 결과 한 행의 grain을 먼저 문장과 key로 정의하는 것이다. 1:N JOIN으로 parent 값이 반복되는지 확인하고 여러 관계는 각각 목표 grain으로 먼저 집계한다. 비집계 컬럼의 결정성, WHERE와 HAVING, 조건부 집계의 NULL, 날짜 timezone까지 명시해야 숫자가 맞는 이유를 설명할 수 있다.

관련 노트

• SQL JOIN
• 정규화